﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;

namespace KHAMBENH
{
    public partial class frmKhambenh : Form
    {
        private Modules.ClickButtonStatus flagButton = Modules.ClickButtonStatus.Ngung;
        public string KEYSL;
        public frmKhambenh()
        {
            InitializeComponent();
        }

        private void frmKhambenh_Load(object sender, EventArgs e)
        {
            GetThuoc();
            GetKhoa();
            FormatFa();
            ClearControl();
        }
        private void FormatFa()
        {
            fa.Cols[1].Editor = cboThuoc;
            fa.Cols[1].Editor.Width = 400;
        }
        private void GetKhoa()
        {
            string connetionString = null;
            string sql = "select tenkhoa,makhoa from khoa";
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            DataSet myDataSet = new DataSet();
            cnn = new OleDbConnection(connetionString);
            try
            {
                OleDbCommand myAccessCommand = new OleDbCommand(sql, cnn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                cnn.Open();
                myDataAdapter.Fill(myDataSet, "khoa");
                cboKhoa.DataSource = myDataSet.Tables["khoa"];
                cboKhoa.Splits[0].DisplayColumns[0].Width = 230;
                cboKhoa.Splits[0].DisplayColumns[1].Width = 50;
               
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }


        }
        private void GetThuoc()
        {
            string connetionString = null;
            string sql = "select thuoc,dvt from tenthuoc";
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            DataSet myDataSet = new DataSet();
            cnn = new OleDbConnection(connetionString);
            try
            {
                OleDbCommand myAccessCommand = new OleDbCommand(sql, cnn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                cnn.Open();
                myDataAdapter.Fill(myDataSet, "tenthuoc");
                cboThuoc.DataSource = myDataSet.Tables["tenthuoc"];
                cboThuoc.Splits[0].DisplayColumns[0].Width = 300;
                cboThuoc.Splits[0].DisplayColumns[1].Width = 100;
                //MessageBox.Show("Connection Open ! ");
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }


        }

        private void fa_AfterEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
        {
            if (fa.Cols[e.Col].Name == "tenthuoc" && fa.Rows[e.Row]["tenthuoc"].ToString().Trim() != "")
            {
                for (int i = 1; i < fa.Rows.Count; i++)
                {
                    if (e.Row != i && fa.Rows[i]["tenthuoc"] != null && fa.Rows[e.Row]["tenthuoc"].ToString().Trim() == fa.Rows[i]["tenthuoc"].ToString().Trim())
                    {
                        MessageBox.Show("Tên thuốc bị trùng vui lòng nhập lại");
                        fa.Rows[e.Row]["tenthuoc"] = "";
                        return;
                    }

                }
                fa.Rows[e.Row]["dvt"] = cboThuoc.Columns["dvt"].Value;
            }
        }

       

        //private void fa_KeyUp(object sender, KeyEventArgs e)
        //{
        //    if (fa.Selection.c1 == 1 && e.KeyCode == Keys.Enter)
        //    {
        //        fa.Select(fa.Selection.r1-1, 3);
        //        fa.StartEditing(fa.Selection.r1, 3);

        //    }
        //}
        private void ClearControl()
        {
            
            txtHoten.Text = "";
            txtGioitinh.Text = "1";
            txtNgaysinh.Text = "1";
            txtThangsinh.Text = "1";
            txtNamsinh.Text = "";
            txtNgaykham.Value = DateTime.Now;
            txtDiachi.Text = "";
            txtLoidan.Text = "";
            txtTenbenh.Text = "";
            fa.Rows.Count = 1;
            fa.Rows.Count = 2;
            cboThuoc.SelectedIndex = 1;
            txtHoten.Focus();
            flagButton = Modules.ClickButtonStatus.Them;
        }
        public void SetValuetoControl()
        {
            string connetionString = null;
            string sql = "select * from DONTHUOC where keysl = '" + KEYSL + "';";
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            DataSet myDataSet = new DataSet();
            cnn = new OleDbConnection(connetionString);
            try
            {
                OleDbCommand myAccessCommand = new OleDbCommand(sql, cnn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                cnn.Open();
                myDataAdapter.Fill(myDataSet, "donthuoc");
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
            txtHoten.Text = myDataSet.Tables["donthuoc"].Rows[0]["hoten"].ToString();
            txtGioitinh.Text = myDataSet.Tables["donthuoc"].Rows[0]["gioitinh"].ToString();
            txtNgaysinh.Text = myDataSet.Tables["donthuoc"].Rows[0]["ngaysinh"].ToString();
            txtThangsinh.Text = myDataSet.Tables["donthuoc"].Rows[0]["thangsinh"].ToString();
            txtNamsinh.Text = myDataSet.Tables["donthuoc"].Rows[0]["namsinh"].ToString();
            txtNgaykham.Value = Convert.ToDateTime(myDataSet.Tables["donthuoc"].Rows[0]["ngaykham"]);
            txtDiachi.Text =  myDataSet.Tables["donthuoc"].Rows[0]["diachi"].ToString();
            txtLoidan.Text = myDataSet.Tables["donthuoc"].Rows[0]["loidan"].ToString();
            txtTenbenh.Text =  myDataSet.Tables["donthuoc"].Rows[0]["chuandoan"].ToString();
            cboKhoa.SelectedIndex = Convert.ToInt16(myDataSet.Tables["donthuoc"].Rows[0]["khoa"]) - 1;
            
            txtHoten.Focus();
            sql = "select * from DONTHUOC_DTL where keygh = '" + KEYSL + "';";
            try
            {
                OleDbCommand myAccessCommand = new OleDbCommand(sql, cnn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                cnn.Open();
                myDataAdapter.Fill(myDataSet, "donthuoc_dtl");
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
            int j = 1;
            fa.Rows.Count = 1;
            for (int i = 0; i < myDataSet.Tables["donthuoc_dtl"].Rows.Count; i++)
            {
                fa.Rows.Count = fa.Rows.Count + 1;
                fa.Rows[j]["tenthuoc"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["tenthuoc"];
                fa.Rows[j]["dvt"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["dvt"];
                fa.Rows[j]["sang"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["sang"];
                fa.Rows[j]["trua"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["trua"];
                fa.Rows[j]["chieu"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["chieu"];
                fa.Rows[j]["toi"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["toi"];
                fa.Rows[j]["ghichu"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["ghichu"];
                fa.Rows[j]["sl"] = myDataSet.Tables["donthuoc_dtl"].Rows[i]["sl"];
                j++;
                //fa.Rows.Count = fa.Rows.Count + 1;

            }
            fa.Rows.Count = fa.Rows.Count + 1;
            flagButton = Modules.ClickButtonStatus.Sua;
        }
        private void btnMoi_Click(object sender, EventArgs e)
        {
            ClearControl();
        }
        
        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void InsertRecord()
        {
            string connetionString = null;
            string sql = "";
            KEYSL = Modules.CreateRunninNo(new Random());
            string strNgaykham = Convert.ToDateTime(txtNgaykham.Value).Day + "/" + Convert.ToDateTime(txtNgaykham.Value).Month
                + "/" + Convert.ToDateTime(txtNgaykham.Value).Year;
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
           
            cnn = new OleDbConnection(connetionString);
            try
            {

                OleDbCommand myAccessCommand = new OleDbCommand();
                myAccessCommand.Connection = cnn;
                cnn.Open();

                sql = "Insert into DONTHUOC (KEYSL,HOTEN,GIOITINH,NGAYSINH,THANGSINH,NAMSINH,NGAYKHAM,DIACHI,CHUANDOAN,LOIDAN,KHOA) "
                + " values('" + KEYSL + "','" + txtHoten.Text + "','" + txtGioitinh.Text + "','" + txtNgaysinh.Text + "','" + txtThangsinh.Text + "','"
                + txtNamsinh.Text + "','" + strNgaykham + "','" + txtDiachi.Text + "','" + txtTenbenh.Text + "','"
                + txtLoidan.Text + "','" + cboKhoa.Columns["makhoa"].Value + "')";
                myAccessCommand.CommandText = sql;
                myAccessCommand.ExecuteNonQuery();

                for (int i = 1; i < fa.Rows.Count; i++)
                {
                    if (fa.Rows[i]["tenthuoc"] != null)
                    {
                        sql = "Insert into DONTHUOC_DTL(KEYGH,TENTHUOC,DVT,SANG,TRUA,CHIEU,TOI,GHICHU,SL) "
                        + " values('" + KEYSL + "','" + fa.Rows[i]["tenthuoc"].ToString() + "','" + fa.Rows[i]["dvt"].ToString() + "','"
                        + Convert.ToString(fa.Rows[i]["sang"]) + "','" + Convert.ToString(fa.Rows[i]["trua"]) + "','" + Convert.ToString(fa.Rows[i]["chieu"]) + "','" + Convert.ToString(fa.Rows[i]["toi"]) + "','"
                        + Convert.ToString(fa.Rows[i]["ghichu"]) + "','" + Convert.ToString(fa.Rows[i]["SL"]) +"')";
                        myAccessCommand.CommandText = sql;
                        myAccessCommand.ExecuteNonQuery();
                    }

                }
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void UpdateRecord()
        {
            string connetionString = null;
            string sql = "";
            string strNgaykham = Convert.ToDateTime(txtNgaykham.Value).Day + "/" + Convert.ToDateTime(txtNgaykham.Value).Month
                + "/" + Convert.ToDateTime(txtNgaykham.Value).Year;
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            DataSet myDataSet = new DataSet();
            cnn = new OleDbConnection(connetionString);
            try
            {
                OleDbCommand myAccessCommand = new OleDbCommand();
                myAccessCommand.Connection = cnn;
                cnn.Open();
                                
                sql = "Update DONTHUOC set HOTEN = @var1,GIOITINH = @var2,NGAYSINH = @var3,"
                + " THANGSINH = @var4, NAMSINH = @var5,NGAYKHAM = @var6,"
                + " DIACHI = @var7,CHUANDOAN = @var8 ,LOIDAN = @var9,KHOA = @var10 "
                + " where  [KEYSL] = @var11";

                myAccessCommand.Parameters.Add(new OleDbParameter("@var1",txtHoten.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var2",txtGioitinh.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var3",txtNgaysinh.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var4", txtThangsinh.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var5",  txtNamsinh.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var6",  strNgaykham));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var7",  txtDiachi.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var8", txtTenbenh.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var9",  txtLoidan.Text));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var10", cboKhoa.Columns["makhoa"].Value));
                myAccessCommand.Parameters.Add(new OleDbParameter("@var11", KEYSL));
               
                myAccessCommand.CommandText = sql;
               
                myAccessCommand.ExecuteNonQuery();
                myAccessCommand.Parameters.Clear();

                sql = "Delete from DONTHUOC_DTL where [KEYGH] = @var10";
                myAccessCommand.Parameters.Add(new OleDbParameter("@var10", KEYSL));
                myAccessCommand.CommandText = sql;
                myAccessCommand.ExecuteNonQuery();
                myAccessCommand.Parameters.Clear();
                for (int i = 1; i < fa.Rows.Count; i++)
                {
                    if (fa.Rows[i]["tenthuoc"] != null && fa.Rows[i]["tenthuoc"].ToString() != "" )
                    {
                        sql = "Insert into DONTHUOC_DTL(KEYGH,TENTHUOC,DVT,SANG,TRUA,CHIEU,TOI,GHICHU,SL) "
                        + " values('" + KEYSL + "','" + fa.Rows[i]["tenthuoc"].ToString() + "','" + fa.Rows[i]["dvt"].ToString() + "','"
                        + Convert.ToString(fa.Rows[i]["sang"]) + "','" + Convert.ToString(fa.Rows[i]["trua"]) + "','" + Convert.ToString(fa.Rows[i]["chieu"]) + "','" + Convert.ToString(fa.Rows[i]["toi"]) + "','"
                        + Convert.ToString(fa.Rows[i]["ghichu"]) + "','" + Convert.ToString(fa.Rows[i]["SL"]) + "')";
                        myAccessCommand.CommandText = sql;
                        myAccessCommand.ExecuteNonQuery();
                    }

                }
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnGhi_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            if (flagButton == Modules.ClickButtonStatus.Them)
            {
                InsertRecord();
                flagButton = Modules.ClickButtonStatus.Sua;
            }
            else if (flagButton == Modules.ClickButtonStatus.Sua)
            {
                UpdateRecord();
            }
            lblthongbao.Text = "Lưu thông tin thành công";
            timer1.Enabled = true;
            this.Cursor = Cursors.Default;
        }

        private void frmKhambenh_KeyDown(object sender, KeyEventArgs e)
        {
            Modules.SendKey(this, e);
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            lblthongbao.Text = "";
            timer1.Enabled = false;
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            string connetionString = null;
            string sql = "";
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            cnn = new OleDbConnection(connetionString);
            try
            {
                OleDbCommand myAccessCommand = new OleDbCommand();
                myAccessCommand.Connection = cnn;
                cnn.Open();
                sql = "Delete from DONTHUOC_DTL where [KEYGH] = @var10";
                myAccessCommand.Parameters.Add(new OleDbParameter("@var10", KEYSL));
                myAccessCommand.CommandText = sql;
                myAccessCommand.ExecuteNonQuery();
                myAccessCommand.Parameters.Clear();
                sql = "Delete from DONTHUOC where [KEYSL] = @var10";
                myAccessCommand.Parameters.Add(new OleDbParameter("@var10", KEYSL));
                myAccessCommand.CommandText = sql;
                myAccessCommand.ExecuteNonQuery();
                myAccessCommand.Parameters.Clear();
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            lblthongbao.Text = "Xóa thông tin thành công";
            timer1.Enabled = true;
            this.Cursor = Cursors.Default;
        }

        private void btnXem_Click(object sender, EventArgs e)
        {
            DataRow newRow = null;
            Dataset.dsDonthuoc dsDonthuoc = new Dataset.dsDonthuoc();
            newRow = dsDonthuoc.Tables["donthuoc"].NewRow();
            newRow["hoten"] = txtHoten.Text;
            newRow["namsinh"] = txtNamsinh.Text;
            if (txtGioitinh.Text == "0"){newRow["gioitinh"] = "Nữ";} else {newRow["gioitinh"] = "Nam" ;}
            newRow["diachi"] = txtDiachi.Text;
            newRow["chuandoan"] = txtTenbenh.Text;
            newRow["loidan"] = txtLoidan.Text;
            newRow["ngaykham"] = txtNgaykham.Text;
            dsDonthuoc.Tables["donthuoc"].Rows.Add(newRow);
            string strTenthuoc;
            for (int i = 1; i < fa.Rows.Count; i++)
            {
                if (fa.Rows[i]["tenthuoc"] != null && fa.Rows[i]["tenthuoc"] != "")
                {
                    newRow = dsDonthuoc.Tables["donthuoc_dtl"].NewRow();
                    strTenthuoc = fa.Rows[i]["tenthuoc"].ToString() + "\n";
                    if (Convert.ToString(fa.Rows[i]["sang"]) != "") { strTenthuoc = strTenthuoc + "Sáng: " + Convert.ToString(fa.Rows[i]["sang"]) + " " + fa.Rows[i]["dvt"].ToString() + "; "; }
                    if (Convert.ToString(fa.Rows[i]["trua"]) != "") { strTenthuoc = strTenthuoc + "Trưa: " + Convert.ToString(fa.Rows[i]["trua"]) + " " + fa.Rows[i]["dvt"].ToString() + "; "; }
                    if (Convert.ToString(fa.Rows[i]["chieu"]) != "") { strTenthuoc = strTenthuoc + "Chiều: " + Convert.ToString(fa.Rows[i]["chieu"]) + " " + fa.Rows[i]["dvt"].ToString() + "; "; }
                    if (Convert.ToString(fa.Rows[i]["toi"]) != "") { strTenthuoc = strTenthuoc + "Tối: " + Convert.ToString(fa.Rows[i]["toi"]) + " " + fa.Rows[i]["dvt"].ToString() + " " + Convert.ToString(fa.Rows[i]["ghichu"]); }
                    newRow["tenthuoc"] = strTenthuoc;
                    newRow["dvt"] = Convert.ToString(fa.Rows[i]["dvt"]);
                    newRow["SL"] = Convert.ToString(fa.Rows[i]["SL"]);
                    dsDonthuoc.Tables["donthuoc_dtl"].Rows.Add(newRow);                   
                }

            }
            Cursor.Current = Cursors.Default;
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpDonthuoc.rpt");
            cr.SetDataSource(dsDonthuoc);
            f.crpView.ReportSource = cr;
            Button bt = (Button)sender;
            if (bt.Name == "btnIn")
            {
                f.crpView.PrintReport();
            }
            else
            {
                f.Show();
            }
        }
    }
}
